package com.weatherstation.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.weatherstation.entity.SensorData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Delete;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
/**
 * 传感器数据Mapper接口
 */
@Mapper
public interface SensorDataMapper extends BaseMapper<SensorData> {
    
    /**
     * 获取最新的传感器数据
     */
    @Select("SELECT * FROM sensor_data ORDER BY created_at DESC LIMIT 1")
    SensorData findTopByOrderByCreatedAtDesc();
    
    /**
     * 根据时间范围查询传感器数据
     */
    @Select("SELECT * FROM sensor_data WHERE created_at BETWEEN #{startTime} AND #{endTime} ORDER BY created_at DESC")
    List<SensorData> findByCreatedAtBetweenOrderByCreatedAtDesc(
            @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 获取指定时间之后的数据
     */
    @Select("SELECT * FROM sensor_data WHERE created_at > #{time} ORDER BY created_at DESC")
    List<SensorData> findByCreatedAtAfterOrderByCreatedAtDesc(@Param("time") LocalDateTime time);
    
    /**
     * 获取最近N条数据
     */
    @Select("SELECT * FROM sensor_data ORDER BY created_at DESC LIMIT 10")
    List<SensorData> findTop10ByOrderByCreatedAtDesc();
    
    /**
     * 获取指定小时数内的数据
     */
    @Select("SELECT * FROM sensor_data WHERE created_at >= #{startTime} ORDER BY created_at DESC")
    List<SensorData> findRecentData(@Param("startTime") LocalDateTime startTime);
    
    /**
     * 获取每小时平均数据
     */
    @Select("SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') as hour, " +

            "CAST(AVG(temperature) AS DECIMAL(10,2)) as avgTemperature, " +
            "CAST(AVG(humidity) AS DECIMAL(10,2)) as avgHumidity, " +
            "CAST(AVG(pressure) AS DECIMAL(10,2)) as avgPressure, " +
            "CAST(AVG(light_intensity) AS DECIMAL(10,2)) as avgLightIntensity, " +
            "CAST(MIN(temperature) AS DECIMAL(10,2)) as minTemperature, " +
            "CAST(MAX(temperature) AS DECIMAL(10,2)) as maxTemperature " +
            "FROM sensor_data " +
            "WHERE created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') " +
            "ORDER BY hour")
    List<Map<String, Object>> findHourlyAverageData(@Param("startTime") LocalDateTime startTime, 
                                                  @Param("endTime") LocalDateTime endTime);
    
    /**
     * 获取每日平均数据
     */
    @Select("SELECT DATE(created_at) as date, " +

            "CAST(AVG(temperature) AS DECIMAL(10,2)) as avgTemperature, " +
            "CAST(AVG(humidity) AS DECIMAL(10,2)) as avgHumidity, " +
            "CAST(AVG(pressure) AS DECIMAL(10,2)) as avgPressure, " +
            "CAST(AVG(light_intensity) AS DECIMAL(10,2)) as avgLightIntensity, " +
            "CAST(MIN(temperature) AS DECIMAL(10,2)) as minTemperature, " +
            "CAST(MAX(temperature) AS DECIMAL(10,2)) as maxTemperature " +
            "FROM sensor_data " +
            "WHERE created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY date " +
            "ORDER BY date")
    List<Map<String, Object>> findDailyStatistics(@Param("startTime") LocalDateTime startTime, 
                                                @Param("endTime") LocalDateTime endTime);
    
    /**
     * 获取温度范围内的数据数量
     */
    @Select("SELECT COUNT(*) FROM sensor_data WHERE temperature BETWEEN #{minTemp} AND #{maxTemp}")
    Long countByTemperatureRange(@Param("minTemp") Double minTemp, @Param("maxTemp") Double maxTemp);
    
    /**
     * 删除指定时间之前的数据
     */
    @Delete("DELETE FROM sensor_data WHERE created_at < #{time}")
    void deleteByCreatedAtBefore(@Param("time") LocalDateTime time);
    
    /**
     * 获取数据总数
     */
    @Select("SELECT COUNT(*) FROM sensor_data")
    Long getTotalCount();
    
    /**
     * 获取最近24小时的数据趋势
     */
    @Select("SELECT * FROM sensor_data WHERE created_at >= #{startTime} ORDER BY created_at")
    List<SensorData> findLast24HoursData(@Param("startTime") LocalDateTime startTime);
    
    /**
     * 获取指定小时数内的数据（带分页）
     */
    @Select("SELECT * FROM sensor_data WHERE created_at >= #{startTime} ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<SensorData> findRecentDataPaged(@Param("startTime") LocalDateTime startTime, 
                                        @Param("limit") int limit, 
                                        @Param("offset") int offset);
    
    /**
     * 获取指定时间范围内的数据数量
     */
    @Select("SELECT COUNT(*) FROM sensor_data WHERE created_at BETWEEN #{startTime} AND #{endTime}")
    long countByTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 获取每月平均数据
     */
    @Select("SELECT DATE_FORMAT(created_at, '%Y-%m-01') as month, " +
            "CAST(AVG(temperature) AS DECIMAL(10,2)) as avgTemperature, " +
            "CAST(AVG(humidity) AS DECIMAL(10,2)) as avgHumidity, " +
            "CAST(AVG(pressure) AS DECIMAL(10,2)) as avgPressure, " +
            "CAST(AVG(light_intensity) AS DECIMAL(10,2)) as avgLightIntensity, " +
            "CAST(MIN(temperature) AS DECIMAL(10,2)) as minTemperature, " +
            "CAST(MAX(temperature) AS DECIMAL(10,2)) as maxTemperature " +
            "FROM sensor_data " +
            "WHERE created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY month " +
            "ORDER BY month")
    List<Map<String, Object>> findMonthlyStatistics(@Param("startTime") LocalDateTime startTime, 
                                                  @Param("endTime") LocalDateTime endTime);
    
    /**
     * 获取每小时数据（安全版本，直接返回Map）
     */
    @Select("SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') as hour, " +
            "CAST(AVG(temperature) AS DECIMAL(10,2)) as avgTemperature, " +
            "CAST(AVG(humidity) AS DECIMAL(10,2)) as avgHumidity, " +
            "CAST(AVG(pressure) AS DECIMAL(10,2)) as avgPressure, " +
            "CAST(AVG(light_intensity) AS DECIMAL(10,2)) as avgLightIntensity, " +
            "CAST(MIN(temperature) AS DECIMAL(10,2)) as minTemperature, " +
            "CAST(MAX(temperature) AS DECIMAL(10,2)) as maxTemperature " +
            "FROM sensor_data " +
            "WHERE created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') " +
            "ORDER BY hour")
    List<Map<String, Object>> findHourlyData(@Param("startTime") LocalDateTime startTime, 
                                           @Param("endTime") LocalDateTime endTime);
} 